package com.example.mapper;

import com.example.entity.vote.VoteRecording;
import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Map;

@Mapper
public interface VoteMapper {
    @Select("""
            SELECT COALESCE(COUNT(vr.vid), 0)
            FROM game_singer_relative gs LEFT JOIN vote_recording vr
            ON gs.gid = vr.gid AND gs.sid = vr.sid
            WHERE gs.gid = #{gid}
            GROUP BY gs.id, gs.gid, gs.sid;
            """)
    List<Integer> getVoteCountByGid(int gid);

    @Select("""
            select name, count(*) count
            from games g right join vote_recording v
            on g.gid = v.gid
            group by g.gid
            order by count(*)
            limit 1, 8
            """)
    List<Map<String, Integer>> getAllVoteCount();

    @Select("""
            select name, count(*) count
            from singers as s right join vote_recording as v
            on s.sid = v.sid
            group by s.sid
            order by count(*)
            limit 1, 10
            """)
    List<Map<String, Integer>> getAllVoteCountSinger();

    @Select("select count(*) from vote_recording where uid = #{uid}")
    Integer getVoteCountByUid(int uid);

    @Select("select * from vote_recording where gid = #{gid}")
    List<VoteRecording> getVoteRecordingByGid(int gid);

    @Select("select count(*) from vote_recording where uid = #{uid} and gid = #{gid}")
    Integer isVoteReCording(VoteRecording voteRecording);

    @Select("select count(*) from vote_recording where uid = #{uid} and gid = #{gid} and sid = #{sid}")
    Integer isVoteReCordingSid(VoteRecording voteRecording);

    @Insert("insert into vote_recording (uid, gid, sid) VALUES (#{uid}, #{gid}, #{sid})")
    int addVoteRecording(VoteRecording voteRecording);

    @Delete("delete from vote_recording where uid = #{uid} and gid = #{gid} and sid = #{sid}")
    int deleteVoteRecordingByUidAndGid(@Param("sid") int sid, @Param("uid") int uid, @Param("gid") int gid);

    @Delete("delete from vote_recording where gid = #{gid}")
    int deleteVoteRecordingByGid(int gid);

}
